#include"sqlite.h"
#include "mdebug.h"

sqlite3 * open_mydb(const char *path)
{
	sqlite3 *db ;
	if(sqlite3_open(path,&db)!=SQLITE_OK)
	{
		printf("open sqlite database :%s \n",sqlite3_errmsg(db));
		exit(-1);
	}
	return db;
}

int create_table(sqlite3 *db)
{
	int ret;
	char buf[100] = {0};
	char *errmsg;
	sprintf(buf, "create table \"userlist\" (\"type\",\"work_num\",\"passwd\",\"name\",\"pay\",\"post\",\"branch\")");
	ret = sqlite3_exec(db, buf, NULL, NULL, &errmsg);
	if (ret != SQLITE_OK)
	{
		if (0 == strcmp(errmsg, "table \"userlist\" already exists")) return 0;
		printf("create table : %s\n", errmsg);
		return -1;
	}
	return 0;
}

int user_compare(sqlite3 *db,char * work_num,char *passwd,int *type)
{
	int ret,r,c;
	char **resultp;
	char *errmsg;
	char buf[100]={0};
	sprintf(buf,"select * from \"userlist\" where \"work_num\"=\"%s\"",work_num);
	ret=sqlite3_get_table(db,buf,&resultp,&r,&c,&errmsg);
	if(ret!=SQLITE_OK)
	{
		printf("Error! (user info compare) :%s\n",errmsg);
		return -1;
	}

	if(r==0) return LOGIN_NOUSER;	
	
	if(0==strcmp(work_num,*(resultp + 8)))
	{
		if(0==strcmp(passwd,*(resultp + 9))) 
		{
			*type=atoi(*(resultp + 7));
			return 0;}
		else return LOGIN_PWERR;
	}
	else return LOGIN_NOUSER;
}

int inset_user(sqlite3 *db,Mesg *user)
{
	int ret;
	char buf[200];
	char *errmsg;
	sprintf(buf,"insert into \"userlist\" values (\"%d\",\"%s\",\"%s\",\"%s\",\"%d\",\"%s\",\"%s\")",
			user->type,user->work_num, user->passwd,user->name,user->pay,user->post,user->branch);
	ret = sqlite3_exec(db, buf, NULL, NULL, &errmsg);
	if (ret != SQLITE_OK)
	{
		printf("insert new user : %s\n", errmsg);
		return -1;
	}
	return 0;
}

int select_userinfo(sqlite3 *db,Mesg *user)
{
	int ret,r,c;
	char **resultp;
	char *errmsg;
	char buf[100]={0};
	sprintf(buf,"select * from \"userlist\" where \"work_num\"=\"%s\"",user->work_num);
	ret=sqlite3_get_table(db,buf,&resultp,&r,&c,&errmsg);
	if(ret!=SQLITE_OK)
	{
		printf("Error! (user info compare) :%s\n",errmsg);
		return -1;
	}
	if(r==0) return LOGIN_NOUSER;
	if(0==strcmp(user->work_num,*(resultp + 8)))
	{
		user->type=atoi(*(resultp + 7));
		strcpy(user->work_num,*(resultp + 8));
		strcpy( user->passwd,*(resultp + 9));
		strcpy( user->name,*(resultp + 10));
		user->pay=atoi(*(resultp + 11));
		strcpy( user->post,*(resultp + 12));
		strcpy( user->branch,*(resultp + 13));
		return  0;
	}
	else return LOGIN_NOUSER;

}

int NULLCallback(void *para, int f_num, char **f_value, char **f_name)
{}

int passwd_update(sqlite3 *db,char * work_num, char *  passwd)
{
	int ret;
	char buf[100];
	char *errmsg;
	sprintf(buf, "update \"userlist\" set \"passwd\"=\"%s\" where \"work_num\"=\"%s\"", passwd, work_num);
	ret = sqlite3_exec(db, buf, NULLCallback, NULL, &errmsg);
	if (ret != SQLITE_OK)
	{
		printf("update user passwd: %s\n", errmsg);
		return -1;
	}
	return 0;
}

int delete_user(sqlite3 *db,char * work_num)
{
	int ret;
	char buf[100];
	char *errmsg;
	sprintf(buf,"delete from \"userlist\" where \"work_num\"=\"%s\"", work_num);
	ret = sqlite3_exec(db, buf, NULL, NULL, &errmsg);
	if (ret != SQLITE_OK)
	{
		printf("delete user \"%s\": %s\n", work_num, errmsg);
		return -1;
	}
	return 0;
}

int info_update(sqlite3 *db,Mesg *user)
{
	int ret,r,c;
	char **resultp;
	char buf[200];
	char *errmsg;
	sprintf(buf,"select * from \"userlist\" where \"work_num\"=\"%s\"",user->work_num);
	ret=sqlite3_get_table(db,buf,&resultp,&r,&c,&errmsg);
	if(ret!=SQLITE_OK)
	{
		printf("Error! (user info compare) :%s\n",errmsg);
		return -1;
	}
	if(r==0) return LOGIN_NOUSER;
	sprintf(buf, "update \"userlist\" set \"type\"=\"%d\",\"passwd\"=\"%s\",\"name\"=\"%s\",\"pay\"=\"%d\",\"post\"=\"%s\",\"branch\"=\"%s\" where \"work_num\"=\"%s\"",
			user->type, user->passwd,user->name,user->pay,user->post,user->branch,user-> work_num);

	ret = sqlite3_exec(db, buf, NULLCallback, NULL, &errmsg);
	if (ret != SQLITE_OK)
	{
		printf("update user passwd: %s\n", errmsg);
		return -1;
	}
	return 0;
}

int close_mydb(sqlite3  *db)
{
	return  sqlite3_close(db);
}

